﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DBAccess;
using SSPharma.Common;
using SSPharma.Model;
using System.Data.Common;
using SSPharma.Biz.Common;
using System.Transactions;

namespace SSPharma.Biz.DanhMuc
{
    class BizDanhMucResource
    {
        //khai báo hàm Common dùng để lấy các hàm dùng chung
        private BizCommon _BizCommon = new BizCommon();
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// lấy Resource theo mã Resource
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public DataTable ft_GetDataDanhMucResourceTheoMa(string Ma)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT a.*,b.TenKhoanMuc,c.TenTaiKhoan,d.TenDonVi from HT_DM_Resource a left outer join HT_DM_KhoanMuc b on a.KhoanMuc = b.SoKhoanMuc");
            strSQL.Append(" left outer join HT_DM_TaiKhoan c on a.TaiKhoan = c.MaTaiKhoan");
            strSQL.Append(" left outer join HT_DM_DonVi d on a.MaDonVi = d.MaDonVi");
            strSQL.Append(" where MaResource = @Ma");
            SQLServer.Param[] Params = new SQLServer.Param[1];
            Params[0] = new SQLServer.Param("@Ma", Ma);
            return Global.DataFactory.GetDataTable(strSQL.ToString(), Params);
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Lay cau truc de luu du lieu xong database
        /// </summary>
        /// <returns></returns>
        public DataSet fs_LayCauTrucTable(string FormName)
        {
            DataSet dsdata = new DataSet();
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT a.*,b.TenKhoanMuc,c.TenTaiKhoan,d.TenDonVi from HT_DM_Resource a left outer join HT_DM_KhoanMuc b on a.KhoanMuc = b.SoKhoanMuc");
            strSQL.Append(" left outer join HT_DM_TaiKhoan c on a.TaiKhoan = c.MaTaiKhoan");
            strSQL.Append(" left outer join HT_DM_DonVi d on a.MaDonVi = d.MaDonVi");
            strSQL.Append(" where 1=2");
            DataTable dt_Resource = Global.DataFactory.GetDataTable(strSQL.ToString());
            dt_Resource.TableName = "dt_Resource";
            dsdata.Tables.Add(dt_Resource);
            SQLServer.Param[] Params = new SQLServer.Param[4];
            Params[0] = new SQLServer.Param("@FormName", FormName);
            Params[1] = new SQLServer.Param("@MaDonVi", Global.DonVi);
            Params[2] = new SQLServer.Param("@BoPhan", Global.BoPhan);
            Params[3] = new SQLServer.Param("@UserName", Global.UserName);
            DataTable dt_Default = Global.DataFactory.ReturnDataTable("sp_LayDuLieuMacDinh", CommandType.StoredProcedure, Params);
            dt_Default.TableName = "dt_Default";
            dsdata.Tables.Add(dt_Default);
            return dsdata;
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// kiem tra giua counter cu va counter moi
        /// </summary>
        /// <param name="p_sCounterOld"></param>
        /// <param name="p_sTienTo"></param>
        /// <returns></returns>
        public bool fb_KiemTraSoCounter(string p_sCounterOld, string p_sTienTo)
        {
            string p_sCounterNew = _BizCommon.fs_GetCodeId(p_sTienTo);
            if (p_sCounterNew.Equals(p_sCounterOld))
                return false;
            return true;
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Them danh muc resource.
        /// </summary>
        /// <param name="p_dtResource"></param>
        /// <param name="TienTo"></param>
        public void fg_InsertDanhMucResource(DataTable p_dtResource, string TienTo)
        {
            try
            {
                string query_resource = "Select * from HT_DM_Resource where 1 = 2";
                string query_congto = "Select * from HT_CongToHienHanh_" + TienTo + " where 1 = 2";
                if (p_dtResource.Rows[0].RowState == DataRowState.Added)
                {
                    DataTable p_dtCongTo = _BizCommon.ft_GetDataCongToHienHanh(TienTo);
                    // TH01: lỗi
                    if (p_dtCongTo == null)
                    {
                        throw new System.Configuration.Provider.ProviderException("Không lấy được số chứng từ");
                    }
                    // TH01: tạo counter lưu xuống database
                    else if(p_dtCongTo.Rows.Count >0)
                    {
                        using (TransactionScope scope = new TransactionScope())
                        {
                            string p_sCreateCounter = _BizCommon.fs_GetCodeId(TienTo);
                            p_dtResource.Rows[0]["MaResource"] = p_sCreateCounter;
                            p_dtCongTo.Rows[0]["SoHienTai"] = !string.IsNullOrEmpty(p_dtCongTo.Rows[0]["SoHienTai"].ToString()) ? (Convert.ToInt64(p_dtCongTo.Rows[0]["SoHienTai"]) + 1) : 1;
                            Global.DataFactory.RunSaveData(p_dtCongTo, query_congto);
                            Global.DataFactory.RunSaveData(p_dtResource, query_resource);
                            scope.Complete();
                            scope.Dispose();
                        }
                    }
                    // TH03: chưa có counter thì tạo counter đầu tiên
                    else if (p_dtCongTo.Rows.Count == 0)
                    {
                        // chạy hàm tạo counter đầu tiên.
                        string createCounter = _BizCommon.fs_CreateCodeId(TienTo);
                        p_dtCongTo = _BizCommon.ft_GetDataCongToHienHanh(TienTo);
                        using (TransactionScope scope = new TransactionScope())
                        {
                            p_dtResource.Rows[0]["MaResource"] = createCounter;
                            p_dtCongTo.Rows[0]["SoHienTai"] = createCounter;
                            Global.DataFactory.RunSaveData(p_dtCongTo, query_congto);
                            Global.DataFactory.RunSaveData(p_dtResource, query_resource);
                            scope.Complete();
                            scope.Dispose();
                        }
                    }
                   
                }
                else
                {
                    using (TransactionScope scope = new TransactionScope())
                    {
                        Global.DataFactory.RunSaveData(p_dtResource, query_resource);
                        scope.Complete();
                        scope.Dispose();
                    }
                }
            }
            catch (Exception ex)
            {
                StringBuilder strSQL = new StringBuilder();
                strSQL.Append("insert into HT_NhatKyLoi(ManHinh,SoCT,NgayPhatSinh,UserName,MaLoi,DienGiai,TenHam) values(@ManHinh,@SoCT,@NgayPhatSinh,@UserName,@MaLoi,@DienGiai,@TenHam)");
                SQLServer.Param[] Params = new SQLServer.Param[7];
                Params[0] = new SQLServer.Param("@ManHinh", "frmResource");
                Params[1] = new SQLServer.Param("@SoCT", p_dtResource.Rows[0]["MaResource"]);
                Params[2] = new SQLServer.Param("@NgayPhatSinh", LibraryFormatConverter.ConvertStringDDMMYYYYToYYYYMMDD(LibraryFormatConverter.SetStringDateDefault));
                Params[3] = new SQLServer.Param("@UserName", Global.UserName);
                Params[4] = new SQLServer.Param("@MaLoi", "");
                Params[5] = new SQLServer.Param("@DienGiai", ex.Message);
                Params[6] = new SQLServer.Param("@TenHam", "InsertDanhMucResource");
                Global.DataFactory.NonQuery(strSQL.ToString(), Params);
                throw new System.Configuration.Provider.ProviderException(ex.Message);
            }
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Cập nhật danh mục Resource
        /// </summary>
        /// <param name="dt"></param>
        public void fg_UpdateDanhMucResource(DataTable dt)
        {
            try
            {
                string query_Resource = "Select * from HT_DM_Resource where 1 = 2";
                using (TransactionScope scope = new TransactionScope())
                {
                    Global.DataFactory.RunSaveData(dt, query_Resource);
                    scope.Complete();
                }
            }
            catch (Exception ex)
            {
                throw new System.Configuration.Provider.ProviderException(ex.Message);
            }
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Mục đích: lấy danh sách theo danh mục
        /// </summary>
        /// <returns></returns>
        public DataTable ft_GetListdata(IList<string> list, string Orderby)
        {
            string str = string.Join(",", list.ToArray());
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT ");
            strSQL.Append(str);
            strSQL.Append(" from HT_DM_Resource with(nolock)");
            strSQL.Append(!string.IsNullOrEmpty(Orderby) ? (" order by " + Orderby) : "");
            return Global.DataFactory.GetDataTable(strSQL.ToString());
        }
        /// <summary>
        /// User:TAMN 31/07/2014
        /// Lay counter quốc tịch
        /// </summary>
        /// <param name="MaTienTo"></param>
        /// <returns></returns>
        public string fs_LayCongToTuDong(string MaTienTo)
        {
            return _BizCommon.fs_GetCodeId(MaTienTo);
        }
        /// <summary>
        /// User: TAMNN 21/07/2014
        /// Lấy tên đơn vị
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public string ft_GetTenDonViTheoMa(string Ma)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT TenDonVi from HT_DM_DonVi a with(nolock)");
            strSQL.Append(" where a.MaDonVi = @Ma");
            SQLServer.Param[] Params = new SQLServer.Param[1];
            Params[0] = new SQLServer.Param("@Ma", Ma);


            using (DbDataReader dataReader = Global.DataFactory.Reader(strSQL.ToString(), Params))
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        return dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0);
                    }
                }
            }
            return "";
        }
        /// <summary>
        /// User: TAMNN 12/08/2014
        /// Lấy tên mục phí
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public string ft_GetTenKhoanMucTheoMa(string Ma)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT TenKhoanMuc from HT_DM_KhoanMuc a with(nolock)");
            strSQL.Append(" where a.SoKhoanMuc = @Ma");
            SQLServer.Param[] Params = new SQLServer.Param[1];
            Params[0] = new SQLServer.Param("@Ma", Ma);


            using (DbDataReader dataReader = Global.DataFactory.Reader(strSQL.ToString(), Params))
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        return dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0);
                    }
                }
            }
            return "";
        }
        /// <summary>
        /// User: TAMNN 21/07/2014
        /// Lấy tên bộ phận
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public string ft_GetTenTaiKhoanTheoMa(string Ma)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT TenTaiKhoan from HT_DM_TaiKhoan a with(nolock)");
            strSQL.Append(" where a.MaTaiKhoan = @Ma");
            SQLServer.Param[] Params = new SQLServer.Param[1];
            Params[0] = new SQLServer.Param("@Ma", Ma);


            using (DbDataReader dataReader = Global.DataFactory.Reader(strSQL.ToString(), Params))
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        return dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0);
                    }
                }
            }
            return "";
        }


    }
}
